using System;
using System.Data;
using System.Data.OleDb;

namespace Lottery
{
		

	/// <summary>
	/// Provide Base SQL Sever Operations
	/// </summary>
	[Serializable]
	public class SQLHelper
	{
		/// <summary>
		/// Get the SqlConnection
		/// </summary>
		/// <returns>SqlConnection</returns>
		public static OleDbConnection GetConnection()
		{
			OleDbConnection Conn = new OleDbConnection(Config.SQLConnection);
			if (Conn.State != ConnectionState.Open) Conn.Open();
			return Conn;
		}
		/// <summary>
		/// Execute a simple query with return of 1 result value
		/// </summary>
		/// <param name="pQuery">SQL Query</param>
		/// <returns>result value from SQL Query</returns>
		public static string QuerySql(string pQuery)
		{
			using(IDbConnection conn = GetConnection())
			{
				return QuerySql(pQuery, conn);
			}
		}

		/// <summary>
		/// Execute a simple query with return of 1 result value
		/// </summary>
		/// <param name="pQuery">SQL Query</param>
		/// <param name="conn">Connection</param>
		/// <returns>result value from SQL Query</returns>
		public static string QuerySql(string pQuery, IDbConnection conn)
		{
			IDbCommand Command = new OleDbCommand(pQuery, (OleDbConnection)conn);
			return Convert.ToString( Command.ExecuteScalar());
		}
		/// <summary>
		/// Execute SQL Command without any message from Server
		/// </summary>
		/// <param name="pQuery">SQL Query</param>
		/// <returns>Returns -1 if error has occured. 0 - if successful result</returns>
		public static int ExecCommandSql(string pQuery)
		{
			using(IDbConnection conn = GetConnection())
			{
				return ExecCommandSql(pQuery, false, conn);
			}
		}
		/// <summary>
		/// Execute SQL Command
		/// </summary>
		/// <param name="lControl">if defined and fShowMessage is True, return the result message from SQL Server </param>
		/// <param name="pQuery">SQL Query</param>
		/// <param name="fShowMessage">set false if you don't want to show result from SQL Server </param>
		/// <param name="conn">Connection</param>
		/// <returns>Returns -1 if error has occured. 0 - if successful result</returns>
		public static int ExecCommandSql(string pQuery, bool fShowMessage, IDbConnection conn)
		{
			if(conn.State != ConnectionState.Open) conn.Open();
			IDbCommand Command = new OleDbCommand(pQuery, (OleDbConnection)conn);
			int lrez = -1;
			lrez = Command.ExecuteNonQuery(); 
			return lrez;
		}

		public static object ExecuteScalar(string commandText)
		{
			return ExecuteScalar(commandText, GetConnection());
		}

		public static object ExecuteScalar(string commandText, IDbConnection conn)
		{
			if(conn.State!=ConnectionState.Open) conn.Open();
			using(conn)
			{
				OleDbCommand Command = new OleDbCommand(commandText, (OleDbConnection)conn);
				return Command.ExecuteScalar();
			}
		}
		/// <summary>
		/// Fill DataSet from a Query
		/// </summary>
		/// <param name="pQuery">SQL Query</param>
		/// <returns>filled DataSet</returns>
		public static DataSet FillDataSet(string pQuery)
		{
			using(IDbConnection conn = GetConnection())
			{
				return _FillDataSet(pQuery, conn);
			}
		}
		/// <summary>
		/// Fill DataSet from a Query and Raise Exception if error has been found
		/// </summary>
		/// <param name="pQuery"></param>
		/// <param name="conn"></param>
		/// <returns></returns>
		public static DataSet _FillDataSet(string pQuery, IDbConnection conn)
		{
			IDbDataAdapter myCommand = new OleDbDataAdapter(pQuery, (OleDbConnection)conn);
			DataSet ds = new DataSet();
			myCommand.Fill(ds);
			return ds;
		}
		/// <summary>
		/// Fill DataView from a Query
		/// </summary>
		/// <param name="pSQL">SQL Query</param>
		/// <returns>Filled DataView</returns>
		public static DataView _FillDataView(string pSQL)
		{
			return FillDataSet(pSQL).Tables[0].DefaultView;
		}
		public static DataTable GetDataTable(string sql, IDbConnection conn)
		{
			DataTable t = new DataTable();
			new OleDbDataAdapter(sql, (OleDbConnection)conn).Fill(t);
			return t;
		}

		public static DataTable GetDataTable(string sql)
		{
			using(IDbConnection conn = GetConnection())
			{
				return GetDataTable(sql, conn);
			}
		}

		/// <summary>
		/// Get DataRows by SQL Query
		/// </summary>
		/// <param name="pSQL">SQL Query</param>
		/// <param name="conn">OLE DB Connection</param>
		/// <returns>First DataRow</returns>
		public static DataRowCollection GetDataRows(string pSQL, IDbConnection conn)
		{
			return GetDataTable(pSQL, conn).Rows;
		}
		/// <summary>
		/// Get DataRows by SQL Query
		/// </summary>
		/// <param name="pSQL">SQL Query</param>
		/// <returns>First DataRow</returns>
		public static DataRowCollection GetDataRows(string pSQL)
		{
			using(IDbConnection conn = GetConnection())
			{
				return GetDataRows(pSQL, conn);
			}
		}
		/// <summary>
		/// Get first DataRow by SQL Query
		/// </summary>
		/// <param name="pSQL">SQL Query</param>
		/// <param name="conn">OLE DB Connection</param>
		/// <returns>First DataRow</returns>
		public static DataRow GetDataRow(string pSQL, IDbConnection conn)
		{
			DataRowCollection lDRs = GetDataRows(pSQL, conn);
			if (lDRs.Count == 0) return null;
			return lDRs[0];
		}
		/// <summary>
		/// Get first DataRow by SQL Query
		/// </summary>
		/// <param name="pSQL">SQL Query</param>
		/// <returns>First DataRow</returns>
		public static DataRow GetDataRow(string pSQL)
		{
			DataRowCollection lDRs = GetDataRows(pSQL);
			if (lDRs.Count == 0) return null;
			return lDRs[0];
		}

		/// <summary>
		/// Fill DataTable from SQL Query
		/// </summary>
		public static void FillDataTable(DataTable dataTable, string pSQL)
		{
			FillDataTable(dataTable, pSQL, GetConnection());
		}

		public static void FillDataTable(DataTable dataTable, string pSQL, IDbConnection conn)
		{
			OleDbCommand cmd = new OleDbCommand();
			cmd.CommandText = pSQL;
			cmd.Connection = (OleDbConnection)conn;
			if(conn.State!=ConnectionState.Open) conn.Open();
			OleDbDataAdapter da = new  OleDbDataAdapter(cmd);
			da.Fill(dataTable);
			cmd.Connection.Close();
		}
	}
}
